﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using IHISCETeim.Library;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using IHISCETeim.Model;

namespace IHISCETeim.DAL
{
    public class OInspectionDAL
    {
        
        /// <summary>
        /// 返回-1 表示机号已存在
        /// 返回-2 表示仪表号码已存在
        /// </summary>
        /// <param name="obj"></param>
        /// <param name="NGItems"></param>
        /// <returns></returns>
        public static int Add(OInspectionInfo obj,List<ONGInspectionInfo> NGItems)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("OInspection_Add", obj.SerialNumber, obj.MachineType, obj.CabNnumber, obj.Timer, obj.Antifreeze, obj.EngineOil, obj.HydraulicOil, obj.DieselOil, obj.CheckDate,obj.EngineNumber,obj.MeterNumber,obj.SourceType);
            int fromid= Convert.ToInt32(DB.ExecuteScalar(dbCommand));
            if(fromid>0)
            { 
                foreach (ONGInspectionInfo item in NGItems)
                {
                    item.FromId = fromid;
                    AddNGItem(item);
                }
            }
            return fromid;
        }
        public static void AddNGItem(ONGInspectionInfo obj)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("OInspection_AddNGItem", obj.SerialNumber, obj.ItemType, obj.ItemCode, obj.Exceptionrecord,obj.PNO,obj.CodeTag,obj.Station,obj.Supplier,obj.MachineType,obj.BillDate,obj.SourceType,obj.NGClass,obj.DutyWorker,obj.FromId);
            DB.ExecuteNonQuery(dbCommand);
        }

        public static bool Delete(int ID)
        {
            Database DB = DatabaseFactory.CreateDatabase();
            try
            {
                DbCommand dbCommand = DB.GetStoredProcCommand("OInspection_Delete", ID);
                DB.ExecuteNonQuery(dbCommand);
                return true;
            }
            catch
            {
                return false;
            }
        }
        public static IDataReader GetList(int ID)
	    {
		    Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("OInspection_GetList", ID);
            return DB.ExecuteReader(dbCommand);


	    }
        /// <summary>
        /// 获取完检单或完成检查书对应的NG项
        /// </summary>
        /// <param name="SerialNumber"></param>
        /// <param name="SourceType"></param>
        /// <returns></returns>
        public static DataTable GetNGItemList(int FromId, string SourceType)
	    {
		    Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("OInspection_GetNGItemList", FromId, SourceType);
            return DB.ExecuteDataSet(dbCommand).Tables[0];

	    }
        /// <summary>
        /// 获取导出数据
        /// </summary>
        /// <param name="MachineType"></param>
        /// <param name="SerialNumber"></param>
        /// <param name="DownlineDateFrom"></param>
        /// <param name="DownlineDateTo"></param>
        /// <param name="DJNGItemCode"></param>
        /// <returns></returns>
        public static DataTable GetExeclDate(string MachineType, string SerialNumber, string DownlineDateFrom, string DownlineDateTo, string DJNGItemCode)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("OInspection_GetExeclDate", MachineType, SerialNumber, DownlineDateFrom, DownlineDateTo, DJNGItemCode);
            return DB.ExecuteDataSet(dbCommand).Tables[0];
        }
        public static DataTable GetPagination(string SourceType,string MachineType, string SerialNumber, string DownlineDateFrom, string DownlineDateTo, string DJNGItemCode, string BZNGItemCode, int PageIndex, int PageSize, string Sort, ref int TotalCount)
	    {
		    Database DB = DatabaseFactory.CreateDatabase();		
		
            DbCommand dbCommand = DB.GetStoredProcCommand("OInspection_GetPagination");
            DB.AddInParameter(dbCommand, "SourceType", DbType.String, SourceType);
            DB.AddInParameter(dbCommand, "MachineType", DbType.String,MachineType);
            DB.AddInParameter(dbCommand, "SerialNumber", DbType.String,SerialNumber);
            DB.AddInParameter(dbCommand, "DownlineDateFrom", DbType.String,DownlineDateFrom);
            DB.AddInParameter(dbCommand, "DownlineDateTo", DbType.String,DownlineDateTo);
            DB.AddInParameter(dbCommand, "DJNGItemCode", DbType.String,DJNGItemCode);
            DB.AddInParameter(dbCommand, "BZNGItemCode", DbType.String,BZNGItemCode);
            DB.AddInParameter(dbCommand, "PageIndex", DbType.Int32,PageIndex);
            DB.AddInParameter(dbCommand, "PageSize", DbType.Int32,PageSize);
            DB.AddInParameter(dbCommand, "Sort", DbType.String,Sort);
            DB.AddOutParameter(dbCommand, "TotalCount", DbType.Int32,8);
				            DataTable dt=  DB.ExecuteDataSet(dbCommand).Tables[0];
				
            TotalCount=Convert.ToInt32(DB.GetParameterValue(dbCommand,"TotalCount"));
            return dt;
	    }


        public static IDataReader OTallyproject_GetByItemCode(string ItemCode)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("OTallyproject_GetByItemCode", ItemCode);
            return DB.ExecuteReader(dbCommand);
        }

        public static DataTable OTallyproject_GetALL(int itemType)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetSqlStringCommand("select * from OTallyproject where itemtype="+itemType.ToString());
            return DB.ExecuteDataSet(dbCommand).Tables[0];
        }
        public static DataTable OIssueCode_GetALL()
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetSqlStringCommand("select * from OIssueCode");
            return DB.ExecuteDataSet(dbCommand).Tables[0];
        }
        /// <summary>
        /// 获取所有的NG项说明 
        /// </summary>
        /// <returns></returns>
        public static DataTable GetNGExceptionrecordList()
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("OInspection_GetNGExceptionrecordList");
            return DB.ExecuteDataSet(dbCommand).Tables[0];
        }
        #region 报表相关
        /// <summary>
        /// 1.1一次性能检测合格率 31 台, 性能检测总台数:47 台 .一次性能检测合格率：31/47X100%≈66﹪（一次性能检测合格数/性能检测台数X100%）。
        /// </summary>
        /// <param name="monthstart"></param>
        /// <param name="monthend"></param>
        /// <returns></returns>
        public static DataTable OInspection_GetReport1(DateTime monthstart, DateTime monthend)
        { 
             Database DB = DatabaseFactory.CreateDatabase();

             DbCommand dbCommand = DB.GetStoredProcCommand("OInspection_GetReport1", monthstart, monthend);
            return DB.ExecuteDataSet(dbCommand).Tables[0];
        }
        /// <summary>
        /// 1.2 一次出入库检查合格台数：55台，出入库台数：108台 一次出入库合格率：55/108X100%≈50.93﹪（一次出入库合格数/出入库台数X100%）。
        /// </summary>
        /// <param name="monthstart"></param>
        /// <param name="monthend"></param>
        /// <returns></returns>
        public static DataTable OInspection_GetReport2(DateTime monthstart, DateTime monthend)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("OInspection_GetReport2", monthstart, monthend);
            return DB.ExecuteDataSet(dbCommand).Tables[0];
        }
        /// <summary>
        /// 获取指定时间段内机型
        /// </summary>
        /// <param name="monthstart"></param>
        /// <param name="monthend"></param>
        /// <returns></returns>
        public static DataTable OInspection_GetReportMacType3(DateTime monthstart, DateTime monthend)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("OInspection_GetReportMacType3", monthstart, monthend);
            return DB.ExecuteDataSet(dbCommand).Tables[0];
        }
        /// <summary>
        /// 二.各机型不良率
        /// </summary>
        /// <param name="mactype">机型</param>
        /// <param name="ItemType">要获取的项目</param>
        /// <param name="monthstart"></param>
        /// <param name="monthend"></param>
        /// <returns></returns>
        public static object OInspection_GetReport3(string mactype, string ItemType, DateTime monthstart, DateTime monthend)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("OInspection_GetReport3",mactype,ItemType, monthstart, monthend);
            return DB.ExecuteDataSet(dbCommand).Tables[0].Rows[0][0];
        }

        /// <summary>
        /// 三.生产品质信息表
        /// </summary>
        /// <param name="monthstart"></param>
        /// <param name="monthend"></param>
        /// <returns></returns>
        public static DataTable OInspection_GetReport4(DateTime monthstart, DateTime monthend)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("OInspection_GetReport4", monthstart, monthend);
            return DB.ExecuteDataSet(dbCommand).Tables[0];
        }

        /// <summary>
        /// 四.重点问题汇总
        /// </summary>
        /// <param name="monthstart"></param>
        /// <param name="monthend"></param>
        /// <returns></returns>
        public static DataTable OInspection_GetReport5(DateTime monthstart, DateTime monthend)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("OInspection_GetReport5", monthstart, monthend);
            return DB.ExecuteDataSet(dbCommand).Tables[0];
        }

        /// <summary>
        /// 五.外协件（性能测试与出入库检查）品异汇总
        /// </summary>
        /// <param name="monthstart"></param>
        /// <param name="monthend"></param>
        /// <returns></returns>
        public static DataTable OInspection_GetReport6(DateTime monthstart, DateTime monthend)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("OInspection_GetReport6", monthstart, monthend);
            return DB.ExecuteDataSet(dbCommand).Tables[0];
        }
        #endregion

        public static DataTable OInspection_ExportNGListByDate(string MachineType, string SerialNumber, string FormDate, string ToDate)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("OInspection_ExportNGListByDate",MachineType,SerialNumber, FormDate, ToDate);
            return DB.ExecuteDataSet(dbCommand).Tables[0];
        }
    }
}
